#-*-coding:utf-8-*-
from openpyxl import Workbook
from openpyxl import load_workbook
import os,glob

'''
以列表形式返回指定目录下的xlsx文件

'''
def file_list(path):
    # file_list=os.listdir(path)
    file_list=glob.glob('*.xlsx')
    # for file in file_list:
        # if os.path.isdir(file):
            # file_list.remove(file)
            # if os.path.isfile(file):
            # str1=os.path.dirname(file)
            # if file==glob.glob('*.exe') or file==glob.glob('*.py'):
        # if file==glob.glob('*.xlsx'):
        #     file_list=exlist.extend(file)
    return file_list
'''
参数：Excel文件名，默认读取激活sheet
以列表形式按行返回Excel行列表
'''
def read_excel(filename):
    wb=load_workbook(filename)
    # stnames=wb.sheetnames
    ws=wb.active
    rows=ws.max_row
    columns=ws.max_column
    total_list=[]
    for r in range(1,rows+1):
        row_list=[]
        for c in range(1,columns+1):
            row_list.append(ws.cell(r,c).value)
        total_list.append(row_list)
    return total_list
    wb.close()
    # print(row_list)

'''
filename:文件名
data_list:二维列表

'''
def write_excel(filename,data_list):
    wb=Workbook()
    ws = wb.active
    
    for row in data_list:
        print(row)
        ws.append(row)
    print('文件写入完成，一共',len(data_list))
    # for m in range(len(data_list)):
    #     ws.append(data_list[m])
    
    # for r in range(len(data_list)):
    #     # print(data_list)
    #     for c in range(len(data_list[0])):
    #         ws.cell(r+1,c+1).value=data_list[r][c]
    wb.save(filename)

files=file_list("./")
# print(files)
print("需要合并的Excel文件如下：")
for i in range(len(files)):
    print(files[i])

    
i=len(files)
print(i)
file_first=read_excel(files[0])
# print(s)
head=file_first[2]  #读取表头
# print(head)
data=[]
data.append(head)
print(data)
for j in range(0,i):
    data.extend(read_excel(files[j])[3:])
# print(data)
if os.path.exists("final"):
    os.chdir("final")
else:
    os.mkdir("final")
    os.chdir("final")
write_excel('返修表.xlsx',data)
